<?php
	/*
		RatingSQL.php
			Holds all the SQL queries needed in the 5 Star Rating System
	*/

	// Include ezSQL for database calls
	include_once "settings.php";
	include_once "{$_GLOBAL['reunionhome']}/lib/ez_sql_core.php";
	include_once "{$_GLOBAL['reunionhome']}/lib/ez_sql_mysql.php";

	class ReunionSQL
	{
		/*
			Database Connection Information
		*/
		var $dbhost;
		var $dbuser;
		var $dbpass;
		var $dbname;
		var $tbl_prefix;
		var $db;

		function ReunionSQL() {
			global $_GLOBAL;

			$this->dbhost = $_GLOBAL['dbhost'];
			$this->dbuser = $_GLOBAL['dbuser'];
			$this->dbpass = $_GLOBAL['dbpass'];
			$this->dbname = $_GLOBAL['dbname'];
			$this->tbl_prefix = '';
			$this->db = new ezSQL_mysql($this->dbuser, $this->dbpass, $this->dbname, $this->dbhost);
		}

		function getweek($id) {
			$sql = "
				select *
				from reunion_Weeks
				where
					WeekID = $id
				order by DisplayOrder
			";

			// just return the info...
			return $this->db->get_row($sql);
		}

		function getallweeks() {
			$sql = "
				select *
				from reunion_Weeks
				order by DisplayOrder
			";

			return $this->db->get_results($sql);
		}

		function setweek($id, $opts) {
			$sql = "
				update reunion_Weeks
				set <options>
				where
					WeekID = $id
			";

			$options = "";
			foreach($opts as $key => $opt)
			{
				list($type, $id) = explode("_", $key);
				if($type = "str")
				{
					$options .= "$id = '$opt', ";
					continue;
				}

				$options .= "$id = $opt";
			}
			$options = str_replace(", ", "", $options);

			$sql = str_replace("<options>", $options, $sql);
			$this->db->query($sql);
		}

		function getparticipant($id) {
			$sql = "
				select *
				from reunion_Participants
				where
					ParticipantID = $id
			";

			// just return the info...
			return $this->db->get_row($sql);
		}

		function getallparticipants() {
			$sql = "
				select *
				from reunion_Participants
				order by Name
			";

			return $this->db->get_results($sql);
		}

		function setparticipant($id, $opts) {
			$sql = "
				update reunion_Participants
				set <options>
				where
					ParticipantID = $id
			";

			$options = "";
			foreach($opts as $key => $opt)
			{
				list($type, $id) = explode("_", $key);
				if($type = "str")
				{
					$options .= "$id = '$opt', ";
					continue;
				}

				$options .= "$id = $opt";
			}
			$options = str_replace(", ", "", $options);

			$sql = str_replace("<options>", $options, $sql);

			$this->db->query($sql);
		}

		function addweek($date = "", $event = "") {
			$ts = date("Y-m-d H:i:s");
			$neworder = $this->lastorder() + 1;
			$sql = "
				insert into reunion_Weeks (Date, Event, TimeStamp, DisplayOrder)
				values(<values>)
			";

			$values = "";
			$values .= $date != "" ? "'$date', " : "'New Date', ";
			$values .= $event != "" ? "'$event', " : "'No Events', ";
			$values .= "'$ts', ";
			$values .= "$neworder";

			$sql = str_replace("<values>", $values, $sql);

			$this->db->query($sql);
			return $this->db->get_var("Select WeekID from reunion_Weeks where TimeStamp = '$ts'");
		}

		function addparticipant($name = "", $priority = "") {
			$ts = date("Y-m-d H:i:s");
			$sql = "
				insert into reunion_Participants (Name, Priority, TimeStamp)
				values(<values>)
			";

			$values = "";
			$values .= $name != "" ? "'$name', " : "'New Person', ";
			$values .= $priority != "" ? "$priority, " : "0, ";
			$values .= "'$ts'";

			$sql = str_replace("<values>", $values, $sql);

			$this->db->query($sql);
			return $this->db->get_var("Select ParticipantID from reunion_Participants where TimeStamp = '$ts'");
		}

		function deleteweek($id) {
			$sql = "
				delete from reunion_ParticipantWeeks
				where
					WeekID = $id
			";

			$this->db->query($sql);

			$sql = "
				delete from reunion_Weeks
				where
					WeekID = $id
			";

			$this->db->query($sql);

			// update the display order..
			$this->updateorder();
		}

		function deleteparticipant($id) {
			$sql = "
				delete from reunion_ParticipantWeeks
				where
					ParticipantID = $id
			";

			$this->db->query($sql);

			$sql = "
				delete from reunion_Participants
				where
					ParticipantID = $id
			";

			$this->db->query($sql);
		}

		function addunavailability($pid, $wkid) {
			$sql = "
				insert into reunion_ParticipantWeeks (ParticipantID, WeekID)
				values ($pid, $wkid)
			";

			$this->db->query($sql);
		}

		function deleteunavailability($pid, $wkid) {
			$sql = "
				delete from reunion_ParticipantWeeks
				where
					ParticipantID = $pid and
					WeekID = $wkid
			";

			$this->db->query($sql);
		}

		function getunavailability($pid) {
			$sql = "
				select WeekID
				from reunion_ParticipantWeeks
				where
					ParticipantID = $pid
			";

			return $this->db->get_results($sql);
		}

		function lastorder() {
			$sql = "
				select max(DisplayOrder)
				from reunion_Weeks
			";

			return $this->db->get_var($sql);
		}

		function clearunavailability($pid) {
			$sql = "
				delete from reunion_ParticipantWeeks
				where
					ParticipantID = $pid
			";

			$this->db->query($sql);
		}

		function translateorder($orderid) {
			$sql = "
				select WeekID
				from reunion_Weeks
				where
					DisplayOrder = $orderid
			";

			return $this->db->get_var($sql);
		}

		function updateorder() {
			$sql = "
				select WeekID, DisplayOrder
				from reunion_Weeks
				order by DisplayOrder
			";

			$wks = $this->db->get_results($sql);
			$cnt = 1;
			foreach($wks as $wk)
			{
				$this->setweek($wk->WeekID, array("int_DisplayOrder" => $cnt));
				$cnt++;
			}
		}

		function unavailablebyweek($wid) {
			$sql = "
				select *
				from reunion_ParticipantWeeks as pw, reunion_Participants as p
				where
					pw.WeekID = $wid and
					pw.ParticipantID = p.ParticipantID
				order by p.Priority DESC, p.Name
			";

			return $this->db->get_results($sql);
		}

		function totalparticipants() {
			$sql = "
				select count(*)
				from reunion_Participants
			";

			return $this->db->get_var($sql);
		}
	}

//	$r = new ReunionSQL();
//
//	print_r($_GLOBAL);
//
//	$people = $r->getallparticipants();
//	print_r($people);
//
//	$wid = $r->addweek();
//	echo "My WeekID: $wid<br/>";
//
//	$r->setweek($wid, array("str_Date" => "Jun 13 - Jun 26"));
//	$mywkinfo = $r->getweek($wid);
//	print_r($mywkinfo);
//	echo "<br/>";
//
//	$pid = $r->addparticipant("John Doe");
//	echo "My Participant: $pid<br/>";
//
//	$r->setparticipant($pid, array("int_Priority" => "1"));
//	$mypinfo = $r->getparticipant($pid);
//	print_r($mypinfo);
//	echo "<br/>";
//
//	$r->addunavailability($pid, $wid);
//	$r->deleteunavailability($pid, $wid);
//
//	$r->deleteparticipant($pid);
//	$r->deleteweek($wid);
?>